const Database = require('better-sqlite3');
const path = require('path');
const fs = require('fs');

// 获取数据库路径
const dbPath = process.env.DATABASE_PATH || './data/freight.db';

// 初始化数据库连接
let db;
try {
  // 确保目录存在
  const dir = path.dirname(dbPath);
  if (!fs.existsSync(dir)) {
    fs.mkdirSync(dir, { recursive: true });
  }
  
  db = new Database(dbPath, { verbose: process.env.NODE_ENV === 'development' ? console.log : null });
  console.log('📦 数据库连接成功');
} catch (err) {
  console.error('❌ 数据库连接失败:', err.message);
  throw err;
}

// 初始化数据库表结构
const initDatabase = () => {
  try {
    // 省份表
    db.prepare(`
      CREATE TABLE IF NOT EXISTS provinces (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL UNIQUE,
        calc_type TEXT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      )
    `).run();

    // 费率段表
    db.prepare(`
      CREATE TABLE IF NOT EXISTS freight_rates (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        province_id INTEGER NOT NULL,
        weight_min REAL NOT NULL,
        weight_max REAL,
        rate REAL NOT NULL,
        base_fee REAL DEFAULT 0,
        sort_num INTEGER NOT NULL DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (province_id) REFERENCES provinces(id) ON DELETE CASCADE,
        UNIQUE(province_id, sort_num)
      )
    `).run();

    // 创建索引
    db.prepare(`CREATE INDEX IF NOT EXISTS idx_freight_rates_province ON freight_rates(province_id)`).run();
    
    console.log('📋 数据库表结构初始化完成');
  } catch (err) {
    console.error('❌ 数据库初始化失败:', err.message);
    throw err;
  }
};

// 数据库操作工具
const dbUtils = {
  // 执行查询并返回所有结果
  all: (sql, params = []) => {
    return db.prepare(sql).all(params);
  },
  
  // 执行查询并返回第一条结果
  get: (sql, params = []) => {
    return db.prepare(sql).get(params);
  },
  
  // 执行插入并返回新记录ID
  run: (sql, params = []) => {
    return db.prepare(sql).run(params);
  },
  
  // 批量插入
  batchInsert: (sql, paramsArray = []) => {
    const stmt = db.prepare(sql);
    const transaction = db.transaction((paramsList) => {
      for (const params of paramsList) {
        stmt.run(params);
      }
    });
    transaction(paramsArray);
  },
  
  // 关闭数据库连接
  close: () => {
    if (db) {
      db.close();
      console.log('🔌 数据库连接已关闭');
    }
  }
};

module.exports = {
  db,
  initDatabase,
  ...dbUtils
};
